<?php

namespace App\Admin\Controllers;

use Encore\Admin\Auth\Database\Administrator;
use Encore\Admin\Layout\Content;
use App\Http\Controllers\Controller;
use Encore\Admin\Form;
use Encore\Admin\Facades\Admin;
use App\Models\Squad;
use http\Env\Response;
use Illuminate\Http\Request;
use Encore\Admin\Grid;
use App\Models\Class_type;
use App\Models\Class_section_group;
use Illuminate\Support\Facades\DB;
use App\Models\Regulate;
use App\Models\Notice;



class RegulateController extends Controller
{
    private $class;


    public function __construct(Regulate $regulate)
    {
        $this->class = $regulate;
    }

    /**
     * Index interface.
     *
     * @return Content
     */
    public function index(Content $content)
    {
        $content->header('德育监管');
        $content->description('详情');
        $content->row($this->form());
        $content->row($this->view());
        return $content;
    }

    /**
     * Edit interface.
     *
     * @param $id
     * @return Content
     */
    public function edit($id)
    {

    }

    /**
     * Create interface.
     *
     * @return Content
     */
    public function create()
    {

    }

    /**
     * Make a grid builder.
     *
     * @return Grid
     */
    protected function grid()
    {

    }

    /**
     * Make a form builder.
     *
     * @return Form
     */
    protected function form()
    {
        return Admin::form(Squad::class,function (Form $form) {
            $form->setTitle('查看');
            $form->row(function ($row) use ($form)
            {
                $user_id = Admin::user()->id;
                $row->width(4)->select('type',trans('admin.type'))->options(function(){
                    return Class_type::options(2);
                })->attribute(['id'=>'type']);
                $row->width(4)->select('section', trans('admin.section'))->options(function(){
                    return Class_section_group::regulate_options();
                })->attribute(['id'=>'section'])->Myload('group', '/api/get_group_user',$user_id);
                $row->width(4)->select('group',trans('admin.group'))->options()->attribute(['id'=>'group']);
            },  $form);

            $form->row(function ($row) use ($form)
            {
                $row->width(4)->text('batch','开班批次')->attribute(['id'=>'batch']);
                //$row->width(4)->text('start_time','开始时间(包含)')->attribute(['id'=>'startTime']);
                //$row->width(4)->text('end_time','结束时间(包含)')->attribute(['id'=>'endTime']);
                $row->width(4)->date('start_time','开始时间(包含)')->default(date('Y-m-d',strtotime('-1 month')))->attribute(['id'=>'startTime']);
                $row->width(4)->date('end_time','结束时间(包含)')->default(date('Y-m-d'))->attribute(['id'=>'endTime']);
            },  $form);

            $form->row(function ($row) use ($form)
            {
                $row->width(4)->select('class_teacher','班主任')->options(function(){
                    return Administrator::where('id','!=',1)->get()->pluck('username','id');
                })->attribute(['id'=>'tutor']);
                $row->width(4)->html('<div></div><button style="margin-left:120px;" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);

            $form->disableSubmit();
            $form->disableReset();
            $form->tools(function (Form\Tools $tools) {
                // 去掉`列表`按钮
                $tools->disableList();
                // 去掉`删除`按钮
                $tools->disableDelete();
                // 去掉`查看`按钮
                $tools->disableView();
            });
        });
    }

    protected function detail_form($time_arr,$teacher)
    {
        return Admin::form(Squad::class,function (Form $form) use($time_arr,$teacher) {
            $form->setTitle('个人详情');
            $form->row(function ($row) use ($form,$time_arr)
            {
                $row->width(3)->date('start_time','开始时间(包含)')->default($time_arr['start_time'])->attribute(['id'=>'startTime']);
                $row->width(3)->date('end_time','结束时间(包含)')->default($time_arr['end_time'])->attribute(['id'=>'endTime']);
                $row->width(4)->html('<button style="" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);

            $form->row(function ($row) use ($form,$teacher)
            {
                $user_id = Admin::user()->id;
                $row->width(3)->select('squad',trans('admin.squad'))->options(function() use ($teacher){
                    $data = array();
                    foreach($teacher as $v){
                        $data[$v->squad_id] = $v->name;
                    }
                    return $data;
                })->attribute(['id'=>'squad']);
                $row->width(3)->select('type', '分类')->options([
                    '0'=>'全部',
                    '1'=>'考勤',
                    '2'=>'测试',
                    '3'=>'家校',
                    '4'=>'听写',
                    '5'=>'作业'
                ])->attribute(['id'=>'type']);
            },  $form);


            $form->disableSubmit();
            $form->disableReset();
            $form->tools(function (Form\Tools $tools) {
                // 去掉`列表`按钮
                $tools->disableList();
                // 去掉`删除`按钮
                $tools->disableDelete();
                // 去掉`查看`按钮
                $tools->disableView();
            });
        });
    }

    protected function list()
    {
        return Admin::content(function (Content $content) {

            $content->header(trans('报表列表'));
            $content->description(trans('列表'));

            $content->body($this->grid()->render());
        });
    }

    protected function view()
    {
        return view('admin.regulate');
    }

    public function detail_submit(Request $request)
    {
        $kaoqin = $test = array();
        $where = array();
        if(isset($request['squad']) && $request['squad'] > 0){
            $where[] = array('s.id','=',$request['squad']);
        }
        if(isset($request['type']) && $request['type'] > 0){
            $where[] = array('n.notice_type','=',$request['type']);
        }

        $kaoqin = DB::table('notices as n')
            ->join('squads as s','s.id','=','n.squad_id')
            ->where($where)
            ->where('n.tutor_id',$request['id'])->whereIn('n.status',['3','4'])
            ->whereIn('n.notice_type',['1','3','4','5'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
            ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->get();

        $test = DB::table('notices as n')->join('test_assigns as ta','n.test_assign_id','=','ta.id')
            ->join('squads as s','s.id','=','ta.squad_id')
            ->where($where)
            ->where('s.class_teacher',$request['id'])->whereIn('n.status',['3','4'])
            ->where('n.notice_type','2')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
            ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->get();

        $data = array();
        foreach($kaoqin as $v){
            $temp = array();
            if($v->status == 4){
                $temp['status']='补录完成';
                $temp['time']=$v->operatetime;
            }elseif($v->status == 3){
                $temp['status']='逾期未完成';
                $temp['time']='';
            }

            if($v->notice_type == 1){
                $temp['type']='考勤';
            }elseif($v->notice_type == 3){
                $temp['type']='家校';
            }elseif($v->notice_type == 4){
                $temp['type']='听写';
            }elseif($v->notice_type == 5){
                $temp['type']='作业';
            }

            $temp['event'] = $v->event;
            $temp['name'] = $v->name;
            $data[] = $temp;
        }

        foreach($test as $v){
            $temp = array();
            if($v->status == 4){
                $temp['status']='补录完成';
                $temp['time']=$v->operatetime;
            }elseif($v->status == 3){
                $temp['status']='逾期未完成';
                $temp['time']='';
            }

            $temp['type'] = '测试';
            $temp['event'] = $v->event;
            $temp['name'] = $v->name;
            $data[] = $temp;
        }
        $count = count($data);
        $total['start_time'] = $request['start_time'];
        $total['end_time'] = $request['end_time'];
        $total['num'] = $count;
        return array('data'=>$data,'total'=>$total);
    }
    protected function detail_view($request){
        if (isset($request['notice_type'])) {
            
        }
        if ($request['notice_type']=='0') {
            if ($request['type']=='1') {
                $result = DB::table('notices as n')
                ->join('squads as s','s.id','=','n.squad_id')
                ->where('n.tutor_id',$request['id'])
                ->whereIn('n.status',['3','4'])
                ->where('n.notice_type','!=','6')
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
                ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->orderBy('s.name')->get();
            }else{
                $result = DB::table('notices as n')
                ->join('squads as s','s.id','=','n.squad_id')
                ->where('n.tutor_id',$request['id'])
                ->where('n.status','3')
                ->where('n.notice_type','!=','6')
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
                ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->orderBy('s.name')->get();
            }
        }else{
            if ($request['type']=='1') {
                $result = DB::table('notices as n')
                ->join('squads as s','s.id','=','n.squad_id')
                ->where('n.tutor_id',$request['id'])->whereIn('n.status',['3','4'])
                ->where('n.notice_type',$request['notice_type'])
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
                ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->orderBy('s.name')->get();
            }else{
                $result = DB::table('notices as n')
                ->join('squads as s','s.id','=','n.squad_id')
                ->where('n.tutor_id',$request['id'])->where('n.status','3')
                ->where('n.notice_type',$request['notice_type'])
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) <='.strtotime($request['end_time']))
                ->whereRaw('UNIX_TIMESTAMP(n.starttime) >='.strtotime($request['start_time']))
                ->select('n.status','n.operatetime','n.event','n.notice_type','s.name')->orderBy('s.name')->get();
            }     
        }



        $data = array();
        foreach($result as $v){
            $temp = array();
            if($v->status == 4){
                $temp['status']='补录完成';
                $temp['operatetime']=$v->operatetime;
            }elseif($v->status == 3){
                $temp['status']='逾期未完成';
                $temp['operatetime']='';
            }

            if($v->notice_type == 1){
                $temp['type']='考勤';
            }elseif($v->notice_type == 2){
                $temp['type']='测试';
            }elseif($v->notice_type == 3){
                $temp['type']='家校';
            }elseif($v->notice_type == 4){
                $temp['type']='听写';
            }elseif($v->notice_type == 5){
                $temp['type']='作业';
            }

            $temp['event'] = $v->event;
            $temp['name'] = $v->name;
            $data[] = $temp;
        }

        $count = count($data);
        $total['start_time'] = $request['start_time'];
        $total['end_time'] = $request['end_time'];
        $total['num'] = $count;
        return view('admin.regulate_detail',compact('data','total'));
    }

    public function submit(Request $request)
    {
        $where = array();
        if(isset($request['type']) && $request['type'] > 0){
            $where[] = array('type','=',$request['type']);
        }
        if(isset($request['section']) && $request['section'] > 0){
            $where[] = array('section','=',$request['section']);
        }
        if(isset($request['group']) && $request['group'] > 0){
            $where[] = array('group','=',$request['group']);
        }
        if(isset($request['batch']) && $request['batch'] > 0){
            $where[] = array('batch','=',$request['batch']);
        }
        if(isset($request['tutor']) && $request['tutor'] > 0){
            $where[] = array('class_teacher','=',$request['tutor']);
        }

        //获取到符合条件的班级
        $squads = DB::table('squads')->where($where)->get(['id','class_teacher']);
        //dd($squads);
        $squa_id = array();
        foreach($squads as $v){
            $squa_id[] = $v->id;
        }
        if(count($squa_id) <= 0){
            return array('status'=>-1,'message'=>'没有符合当前条件的班级');
        }

        $monthBegin  = '';
        //$weekBegin = $weekEnd = $monthBegin  = '';
        $time = DB::table('terms')->where('status','=','0')->get(['start_date']);
        $time1 = '';
        foreach($time as $v){
            $time1 = strtotime($v->start_date);
        }

        //周的时间计算出来，
        // $weekEnd = date('Y-m-d');
        // $weekBegin = (strtotime($weekEnd) - $time1 ) / 86400 % 7;
        // switch($weekBegin){
        //     case 0:
        //         $Begin = strtotime($weekEnd);
        //         break;
        //     case 1:
        //         $Begin = strtotime($weekEnd.' -1 day');
        //         break;
        //     case 2:
        //         $Begin = strtotime($weekEnd.' -2 days');
        //         break;
        //     case 3:
        //         $Begin = strtotime($weekEnd.' -3 days');
        //         break;
        //     case 4:
        //         $Begin = strtotime($weekEnd.' -4 days');
        //         break;
        //     case 5:
        //         $Begin = strtotime($weekEnd.' -5 days');
        //         break;
        //     case 6:
        //         $Begin = strtotime($weekEnd.' -6 days');
        //         break;
        //     default:
        //         break;
        // };
        $monthBegin = strtotime(date('Y-m-01')); //月的时间直接取当月1号开始
        $weekEnd = strtotime(date('Y-m-d'));


        $time_arr = array();
        $time_arr['request']['start'] = strtotime($request['start_time']);
        $time_arr['request']['end'] = strtotime($request['end_time']);
        $time_arr['month']['start'] = $monthBegin;
        $time_arr['month']['end'] = $weekEnd;
        // $time_arr['week']['start'] = $Begin;
        // $time_arr['week']['end'] = $weekEnd;

        $num_a = $this->class->get_kaoqin($squa_id,$time_arr);

        foreach($num_a as $k => $v){
            if(!isset($v['total_pre'])){
                $num_a[$k]['total_pre'] = 0;
            }
            if(!isset($v['attendance_pre'])){
                $num_a[$k]['attendance_pre'] = 0;
            }
            if(!isset($v['test_pre'])){
                $num_a[$k]['test_pre'] = 0;
            }
            if(!isset($v['jiaxiao_pre'])){
                $num_a[$k]['jiaxiao_pre'] = 0;
            }
            if(!isset($v['dictation_pre'])){
                $num_a[$k]['dictation_pre'] = 0;
            }
            if(!isset($v['zuoye_pre'])){
                $num_a[$k]['zuoye_pre'] = 0;
            }
            // if(!isset($v['total_week'])){
            //     $num_a[$k]['total_week'] = 0;
            // }
            // if(!isset($v['attendance_week'])){
            //     $num_a[$k]['attendance_week'] = 0;
            // }
            // if(!isset($v['test_week'])){
            //     $num_a[$k]['test_week'] = 0;
            // }
            // if(!isset($v['jiaxiao_week'])){
            //     $num_a[$k]['jiaxiao_week'] = 0;
            // }
            // if(!isset($v['dictation_week'])){
            //     $num_a[$k]['dictation_week'] = 0;
            // }
            // if(!isset($v['zuoye_week'])){
            //     $num_a[$k]['zuoye_week'] = 0;
            // }
            if(!isset($v['total_month'])){
                $num_a[$k]['total_month'] = 0;
            }
            if(!isset($v['attendance_month'])){
                $num_a[$k]['attendance_month'] = 0;
            }
            if(!isset($v['test_month'])){
                $num_a[$k]['test_month'] = 0;
            }
            if(!isset($v['jiaxiao_month'])){
                $num_a[$k]['jiaxiao_month'] = 0;
            }
            if(!isset($v['dictation_month'])){
                $num_a[$k]['dictation_month'] = 0;
            }
            if(!isset($v['zuoye_month'])){
                $num_a[$k]['zuoye_month'] = 0;
            }
            if(!isset($v['total_not'])){
                $num_a[$k]['total_not'] = 0;
            }
            if(!isset($v['attendance_not'])){
                $num_a[$k]['attendance_not'] = 0;
            }
            if(!isset($v['test_not'])){
                $num_a[$k]['test_not'] = 0;
            }
            if(!isset($v['jiaxiao_not'])){
                $num_a[$k]['jiaxiao_not'] = 0;
            }
            if(!isset($v['dictation_not'])){
                $num_a[$k]['dictation_not'] = 0;
            }
            if(!isset($v['zuoye_not'])){
                $num_a[$k]['zuoye_not'] = 0;
            }
        }

        $tutor = array_keys($num_a);
        $tutor = DB::table('admin_users')->whereIn('id',$tutor)
            ->get(['id','en_name'])->toArray();
        foreach($tutor as $v){
            $num_a[$v->id]['name'] = $v->en_name;
        }

        return $num_a;
    }

    public function detail(Request $request,Content $content){
        $content->header('异常');
        $content->description('详情');

        $teacher = DB::table('admin_users as a')->leftJoin('squads as s','a.id','=','s.class_teacher')
            ->where('a.id',intval($request['id']))->select('a.id','a.en_name','s.name',DB::raw('s.id as squad_id'))->get();
        $html  = '';
        $str = array();
        foreach($teacher as $v){
            if($html == ''){
                $html .= '<div style="margin-bottom:20px">';
                $html .= '<label>班主任:</label>';
                $html .= '<input disabled="disabled" type="text" id="teacher" tutor_id="'.$v->id.'" value="'.$v->en_name.'">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
            }
        }
        $notice_type = $request['notice_type'];
        switch ($notice_type) {
            case 0:
                $notice_type = "考勤+测试+家校+听写+作业";
                break;
            case 1:
                $notice_type = "考勤";
                break;
            case 2:
                $notice_type = "测试";
                break;
            case 3:
                $notice_type = "家校";
                break;
            case 4:
                $notice_type = "听写";
                break;
            case 5:
                $notice_type = "作业";
                break;
            default:
                # code...
                break;
        }
        $html .= '<label>时间段:</label><input disabled="disabled" type="text" value="'.$request['start_time'].'"><span>至</span><input disabled="disabled" type="text" value="'.$request['end_time'].'">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<label>类型：</label><input disabled="disabled" type="text" value="'.$notice_type.'" style="width:200px"></div>';

        //$time_arr['start_time'] = $request['start_time'];
        //$time_arr['end_time'] = $request['end_time'];
        $content->row($html);
        //$content->row($this->detail_form($time_arr,$teacher));
        $content->row($this->detail_view($request));
        return $content;
    }

    public function report(Content $content,Request $request)
    {
        $content->header('德育监管');
        $content->description('详情');
        $content->row($this->report_form($request));
        $content->row(view('admin.regulate_report'));
        return $content;
    }

    protected function report_form($param)
    {
        return Admin::form(Squad::class,function (Form $form) use ($param) {
            $form->setTitle('查看');
            $form->row(function ($row) use ($form)
            {
                $user_id = Admin::user()->id;
                $row->width(4)->select('section', trans('admin.section'))->options(function(){
                    return Class_section_group::regulate_options();
                })->attribute(['id'=>'section'])->default(13);
                $row->width(4)->text('year','学年')->attribute(['id'=>'year'])->default(date('Y'));
                $row->width(4)->select('term','学期')->options(function(){
                    return array('0'=>'全部','1'=>'春季学期','2'=>'秋季学期');
                })->attribute(['id'=>'term']);
            },  $form);

            $form->row(function ($row) use ($form,$param)
            {
                if(isset($param['week']) && $param['week'] > 0){
                    $row->width(4)->select('start_time','开始时间')->options(function(){
                        $arr = array();
                        for($i=1;$i<25;$i++){
                            $arr[$i] = '第'.$i.'周';
                        }
                        return $arr;
                    })->attribute(['id'=>'start_time'])->default($param['week']);
                    $row->width(4)->select('end_time','结束时间')->options(function(){
                        $arr = array();
                        for($i=1;$i<25;$i++){
                            $arr[$i] = '第'.$i.'周';
                        }
                        return $arr;
                    })->attribute(['id'=>'end_time'])->default($param['week']);
                }else{
                    $row->width(4)->select('start_time','开始时间')->options(function(){
                        $arr = array();
                        for($i=1;$i<25;$i++){
                            $arr[$i] = '第'.$i.'周';
                        }
                        return $arr;
                    })->attribute(['id'=>'start_time'])->default(1);
                    $row->width(4)->select('end_time','结束时间')->options(function(){
                        $arr = array();
                        for($i=1;$i<25;$i++){
                            $arr[$i] = '第'.$i.'周';
                        }
                        return $arr;
                    })->attribute(['id'=>'end_time'])->default(1);
                }
                $row->width(4)->html('<button style="margin-left:120px" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);

            $form->disableSubmit();
            $form->disableReset();
            $form->tools(function (Form\Tools $tools) {
                // 去掉`列表`按钮
                $tools->disableList();
                // 去掉`删除`按钮
                $tools->disableDelete();
                // 去掉`查看`按钮
                $tools->disableView();
            });
        });
    }

    public function report_submit(Request $request)
    {
        $query = DB::table('squads as s')->join('Class_section_groups as csg','s.group','=','csg.id')
            ->join('Class_types as ct','s.type','=','ct.id')
            ->join('squad_month as sm','sm.squad_id','=','s.id')
            ->where('s.section',$request['section'])->where('sm.year',$request['year'])
            ->where('sm.week','>=',$request['start_time'])
            ->where('sm.week','<=',$request['end_time']);

        if(isset($request['term']) && $request['term'] > 0){
            $query = $query->where('season',$request['term']);
        }

        $query1_1 = clone $query;

        $group1 = $query1_1->groupBy('s.group','csg.name')->where('kq_rate','>',0)
            ->select(DB::raw('ROUND(AVG(kq_rate),2) as kq'),'s.group','csg.name')
            ->get();
        unset($query1_1);
        $group1 = json_decode(json_encode($group1),true);

        $query1_2 = clone $query;
        $group2 = $query1_2->groupBy('s.group','csg.name')->where('jx_rate','>',0)
            ->select(DB::raw('ROUND(AVG(jx_rate),2) as jx'),'s.group','csg.name')
            ->get();
        unset($query1_2);
        $group2 = json_decode(json_encode($group2),true);

        $query1_3 = clone $query;
        $group3 = $query1_3->groupBy('s.group','csg.name')->where('zy_rate','>',0)
            ->select(DB::raw('ROUND(AVG(zy_rate),2) as zy'),'s.group','csg.name')
            ->get();
        unset($query1_3);
        $group3 = json_decode(json_encode($group3),true);

        $query1_4 = clone $query;
        $group4 = $query1_4->groupBy('s.group','csg.name')
            ->select(DB::raw('SUM(wastage_num) as lost_num'),'s.group','csg.name')
            ->get();
        unset($query1_4);
        $group4 = json_decode(json_encode($group4),true);

        foreach($group1 as $k => $v){
            if(!isset($group1[$k])){
                $group1[$k] = array('kq'=>0);
            }
            if(!isset($group2[$k])){
                $group2[$k] = array('jx'=>0);
            }
            if(!isset($group3[$k])){
                $group3[$k] = array('zy'=>0);
            }
            if(!isset($group4[$k])){
                $group4[$k] = array('lost_num'=>0);
            }
            $group1[$k] = array_merge($group1[$k],$group2[$k],$group3[$k],$group4[$k]);
        }
        unset($group2,$group3,$group4);

        //
        $query2_1 = clone $query;
        $type1 = $query2_1->groupBy('s.type','ct.name')->where('kq_rate','>',0)
            ->select(DB::raw('ROUND(AVG(kq_rate),2) as kq'),'s.type','ct.name')
            ->get();
        unset($query2_1);
        $type1 = json_decode(json_encode($type1),true);

        $query2_2 = clone $query;
        $type2 = $query2_2->groupBy('s.type','ct.name')->where('jx_rate','>',0)
            ->select(DB::raw('ROUND(AVG(jx_rate),2) as jx'),'s.type','ct.name')
            ->get();
        unset($query2_2);
        $type2 = json_decode(json_encode($type2),true);

        $query2_3 = clone $query;
        $type3 = $query2_3->groupBy('s.type','ct.name')->where('zy_rate','>',0)
            ->select(DB::raw('ROUND(AVG(zy_rate),2) as zy'),'s.type','ct.name')
            ->get();
        unset($query2_3);
        $type3 = json_decode(json_encode($type3),true);

        $query2_4 = clone $query;
        $type4 = $query2_4->groupBy('s.type','ct.name')
            ->select(DB::raw('SUM(wastage_num) as lost_num'),'s.type','ct.name')
            ->get();
        unset($query2_4);
        $type4 = json_decode(json_encode($type4),true);

        foreach($type1 as $k => $v){
            if(!isset($type1[$k])){
                $type1[$k] = array('kq'=>0);
            }
            if(!isset($type2[$k])){
                $type2[$k] = array('jx'=>0);
            }
            if(!isset($type3[$k])){
                $type3[$k] = array('zy'=>0);
            }
            if(!isset($type4[$k])){
                $type4[$k] = array('lost_num'=>0);
            }
            $type1[$k] = array_merge($type1[$k],$type2[$k],$type3[$k],$type4[$k]);
        }
        unset($type2,$type3,$type4);

        //
        $query3_1 = clone $query;
        $batch1 = $query3_1->groupBy('s.batch')->where('kq_rate','>',0)
            ->select(DB::raw('ROUND(AVG(kq_rate),2) as kq'),'s.batch as name')
            ->get();
        unset($query3_1);
        $batch1 = json_decode(json_encode($batch1),true);

        $query3_2 = clone $query;
        $batch2 = $query3_2->groupBy('s.batch')->where('jx_rate','>',0)
            ->select(DB::raw('ROUND(AVG(jx_rate),2) as jx'),'s.batch as name')
            ->get();
        unset($query3_2);
        $batch2 = json_decode(json_encode($batch2),true);

        $query3_3 = clone $query;
        $batch3 = $query3_3->groupBy('s.batch')->where('zy_rate','>',0)
            ->select(DB::raw('ROUND(AVG(zy_rate),2) as zy'),'s.batch as name')
            ->get();
        unset($query3_3);
        $batch3 = json_decode(json_encode($batch3),true);

        $query3_4 = clone $query;
        $batch4 = $query3_4->groupBy('s.batch')
            ->select(DB::raw('SUM(wastage_num) as lost_num'),'s.batch as name')
            ->get();
        unset($query3_4);
        $batch4 = json_decode(json_encode($batch4),true);

        foreach($batch1 as $k => $v){
            if(!isset($batch1[$k])){
                $batch1[$k] = array('kq'=>0);
            }
            if(!isset($batch2[$k])){
                $batch2[$k] = array('jx'=>0);
            }
            if(!isset($batch3[$k])){
                $batch3[$k] = array('zy'=>0);
            }
            if(!isset($batch4[$k])){
                $batch4[$k] = array('lost_num'=>0);
            }
            $batch1[$k] = array_merge($batch1[$k],$batch2[$k],$batch3[$k],$batch4[$k]);
        }
        unset($batch2,$batch3,$batch4);

        return array('group'=>$group1,'type'=>$type1,'batch'=>$batch1);
    }

    public function report_export_cond(Content $content)
    {
        $content->header('德育监管');
        $content->description('详情');
        $content->row($this->report_export_cond_form());
        $content->row($this->report_export_cond_view());
        return $content;
    }

    public function report_export_cond_form()
    {
        return Admin::form(Squad::class,function (Form $form){
            $form->setTitle('查看');
            $form->row(function ($row) use ($form)
            {
                $row->width(2)->text('year','年')->attribute(['id'=>'year'])->default(date('Y'));
                $row->width(2)->select('month', '月')->options(function(){
                    $arr = array();
                    for($i=1;$i<=12;$i++){
                        $arr[$i] = $i.'月';
                    }
                    return $arr;
                })->attribute(['id'=>'month'])->default(date('m')-1);
                //$row->width(4)->text('start_time','开始时间(包含)')->attribute(['id'=>'startTime']);
                //$row->width(4)->text('end_time','结束时间(包含)')->attribute(['id'=>'endTime']);
                $row->width(2)->select('status', '状态')->options(function(){
                    return array('0'=>'未完成','1'=>'已完成');
                })->attribute(['id'=>'status']);
                $row->width(2)->date('batch','开班批次')->format('YYYY-MM')->attribute(['id'=>'batch']);
                $row->width(4)->select('tutor', '班主任')->options(function(){
                    $role = DB::table('admin_roles')->where('name','班主任')->get(['id']);
                    $id = '';
                    foreach($role as $v){
                        $id = $v->id;
                    }

                    $tutor = DB::table('admin_users as au')->join('admin_role_users as aru','au.id','=','aru.user_id')
                        ->where('aru.role_id',$id)->select('au.username','au.id')->get();

                    $arr = array();
                    foreach($tutor as $v){
                        $arr[$v->id] = $v->username;
                    }
                    array_unshift($arr,'全部');
                    return $arr;
                })->attribute(['id'=>'tutor']);
            },  $form);


            $form->row(function ($row) use ($form)
            {
                $user_id = Admin::user()->id;
                $row->width(4)->select('section', trans('admin.section'))->options(function(){
                    return Class_section_group::regulate_options();
                })->attribute(['id'=>'section'])->Myload('group', '/api/get_group_user',$user_id);
                $row->width(4)->select('group',trans('admin.group'))->options()->attribute(['id'=>'group']);
                $row->width(4)->select('type',trans('admin.type'))->options(function(){
                    return Class_type::options(2);
                })->attribute(['id'=>'type']);
            },  $form);

            $form->row(function ($row) use ($form)
            {
                $row->width(5)->html('<div></div>');
                $row->width(3)->html('<div></div><button style="margin-left:65px;" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);

            $form->disableSubmit();
            $form->disableReset();
            $form->tools(function (Form\Tools $tools) {
                // 去掉`列表`按钮
                $tools->disableList();
                // 去掉`删除`按钮
                $tools->disableDelete();
                // 去掉`查看`按钮
                $tools->disableView();
            });
        });
    }

    public function report_export_cond_view()
    {
        return view('admin.report_export_cond');
    }

    public function report_export_cond_submit(Request $request)
    {
        $query = DB::table('squads as s')->join('admin_users as au','s.class_teacher','=','au.id');
        if(isset($request['section']) && $request['section'] > 0){
            $query = $query->where('s.section',$request['section']);
        }
        if(isset($request['type']) && $request['type'] > 0){
            $query = $query->where('s.type',$request['type']);
        }
        if(isset($request['group']) && $request['group'] > 0){
            $query = $query->where('s.group',$request['group']);
        }
        if(isset($request['batch']) && $request['batch'] > 0){
            $query = $query->where('s.batch',$request['batch']);
        }
        if(isset($request['tutor']) && $request['tutor'] > 0){
            $query = $query->where('s.class_teacher',$request['tutor']);
        }
        $squads = $query->get(['s.id','au.username','s.name']);

        $squad_id = array();
        foreach($squads as $v){
            $squad_id[$v->id]['tutor'] = $v->username;
        }

        unset($query);

        $query = DB::table('reports')
            ->where('pid','=',0);
        if(isset($request['year']) && $request['year'] > 0){
            $query = $query->where('year',$request['year']);
        }
        if(isset($request['month']) && $request['month'] > 0){
            $query = $query->where('month',$request['month']);
        }
        if(isset($request['status'])){
            $query = $query->where('status',(int)$request['status']);
        }
        if(count($squad_id) > 0){
            $query = $query->whereIn('squad',array_keys($squad_id));
        }
        $detail_data = $query->get();
        $detail_data = json_decode(json_encode($detail_data),true);


        $query1 = DB::table('reports as r1')->join('reports as r2','r1.id','=','r2.pid')
            ->where('r1.pid','=',0);
        if(isset($request['year']) && $request['year'] > 0){
            $query1 = $query1->where('r1.year',$request['year']);
        }
        if(isset($request['month']) && $request['month'] > 0){
            $query1 = $query1->where('r1.month',$request['month']);
        }
        if(isset($request['status'])){
            $query1 = $query1->where('r1.status',(int)$request['status']);
        }
        if(count($squad_id) > 0){
            $query1 = $query1->whereIn('r1.squad',array_keys($squad_id));
        }
        $num_data = $query1->groupBy(['r1.squad','r2.status'])->select(DB::raw('count(1) as num'),'r2.status','r1.squad')->get();

        $num_data_arr = array();
        foreach($num_data as $v){
            if(!isset($num_data_arr[$v->squad])){
                $num_data_arr[$v->squad]['finish'] = 0;
                $num_data_arr[$v->squad]['not'] = 0;
            }
            if($v->status == 1){
                $num_data_arr[$v->squad]['finish'] = $v->num;
            }elseif($v->status == 0){
                $num_data_arr[$v->squad]['not'] = $v->num;
            }
        }

        foreach($detail_data as $k => $v){
            $detail_data[$k]['squad_name'] = explode('-',$v['name'])[0];
            $detail_data[$k]['tutor'] = $squad_id[$v['squad']]['tutor'];
            $detail_data[$k]['title'] = explode('-',$v['name'])[1];
            $detail_data[$k]['cond'] = $num_data_arr[$v['squad']]['finish'].'/'.($num_data_arr[$v['squad']]['finish']+$num_data_arr[$v['squad']]['not']);
        }

        return $detail_data;
    }
}
